package apibasej.db.sql.wrapper;

import java.util.ArrayList;
import java.util.List;

import apibasej.basic.config.ConfigManager;
import apibasej.db.metadata.Col;
import apibasej.db.metadata.Tab;

public class WrapperSQL extends ConfigManager{

	
	// para select, se for vazio (não tiver nenhum WrapperColSelect) faz select * . 
	//se for * a chave do map será o nome da coluna retornado pelo banco
	private ArrayList<ColSelect<?>> colsSelect; 
	
	private BlockWhere blockWhere;
	
	private ArrayList<Entry<?>> entries; 
	// !!! para o group by usar as colsSelect, setar apenas se gera ou não e a ordem em cada colSelect 
	private ArrayList<OrderBy<?>> colsOrderBy; 
	private ArrayList<ColSelect<?>> colsGroupBy;
	
	private ArrayList<WrapperTabJoin> tabsJoin;// as tabelas de join devem ser adicionadas na ordem correta e ter referência para alguma tabela adicionada antes
	private WrapperTab tabRoot;
	
	private boolean permitDeleteUpdateWithoutFiltrers = false;
	private boolean isSelectDistinct = false;
	private boolean isDeleteCascade = false;
	private boolean useAsterisk = false; // se usa select * quando não tiver colunas setadas
	
	
	private String fixedSqlInsert;
	private String fixedSqlUpdate;
	private String fixedSqlSelect;
	private String fixedSqlDelete;
	
	private String lastSql;

	public WrapperSQL(WrapperTab tab) {
		this.tabRoot = tab;
	}
	public WrapperTab getWrapperTabRoot() {
		return tabRoot;
	}
	
	public ArrayList<WrapperTab> getAllTabs(){
		ArrayList<WrapperTab> ts = new ArrayList<WrapperTab>();
		ts.add(getWrapperTabRoot());
		for(WrapperTabJoin tj : getTabsJoin()){
			ts.add(tj);
		}
		return ts;
	}
	
	public WrapperTab getWrapperForTab(Tab t){
		if(getWrapperTabRoot().getTab().equals(t)){
			return getWrapperTabRoot();
		}
		for(WrapperTabJoin tj : getTabsJoin()){
			if(tj.getTab().equals(t)){
				return tj;
			}
		}
		return null;
	}
	
	/*
	public void setTab(WrapperTab tab) throws SQLWrapperException {
		// valida para evitar que tente executar um UtilDB com dados de outra transação anterior
		if(this.tab!=null) throw new SQLWrapperException("Table already was set for this WrapperTab. Before: "+this.tab.getTab().getName()+". Current: "+tab.getTab().getName() );
		else this.tab = tab;
	}
	*/
	
	public String generateSqlInsert() throws SQLWrapperException {
		if(fixedSqlInsert!=null) {
			lastSql = fixedSqlInsert;
		}else{
			/*
			InterceptorWrapperSQL iw = getProp(INTERCEPTOR_WRAPPER_SQL);
			if(iw!=null){
				iw.changeBeforeGenerateInsert(this);
			}*/
			StringBuilder sqlInsert = new StringBuilder("INSERT INTO "+getWrapperTabValidating().getStrForSql()+" (");
			StringBuilder sbParams = new StringBuilder("");
			boolean appendVirg = false;
			for(Entry<?> in : getEntries()){
				if(appendVirg) {
					sqlInsert.append(',');
					sbParams.append(',');
				}
				sqlInsert.append(in.getCol().getName());
				if(in.getValueForConcat()!=null) sbParams.append(in.getValueForConcat());
				else sbParams.append('?');
				appendVirg = true;
			}
			sqlInsert.append(") VALUES (");
			sqlInsert.append(sbParams);
			sqlInsert.append(") ");
			lastSql = sqlInsert.toString();
		}
		return lastSql;
	}
	
	public String generateSqlUpdate() throws SQLWrapperException {
		if(fixedSqlUpdate!=null) {
			lastSql = fixedSqlUpdate;
		}else{
			/*
			InterceptorWrapperSQL iw = getProp(INTERCEPTOR_WRAPPER_SQL);
			if(iw!=null){
				iw.changeBeforeGenerateUpdate(this);
			}*/
			StringBuilder sqlUpdate = new StringBuilder("UPDATE ");
			sqlUpdate.append(getWrapperTabValidating().getStrForSql());
			sqlUpdate.append(" SET");
			boolean appendVirg = false;
			for(Entry<?> in : getEntries()){
				if(appendVirg) sqlUpdate.append(',');
				sqlUpdate.append(' ');
				sqlUpdate.append(in.getCol().getName());
				sqlUpdate.append(" = ");
				if(in.getValueForConcat()!=null) {
					sqlUpdate.append(in.getValueForConcat());
				} else {
					sqlUpdate.append('?');
				}
				appendVirg = true;
			}
			appendWhere(sqlUpdate,false);
			lastSql = sqlUpdate.toString();
		}
		return lastSql;
	}
	
	public String generateSqlDelete() throws SQLWrapperException {
		if(fixedSqlDelete!=null) {
			lastSql = fixedSqlDelete;
		}else{
			/*
			InterceptorWrapperSQL iw = getProp(INTERCEPTOR_WRAPPER_SQL);
			if(iw!=null){
				iw.changeBeforeGenerateDelete(this);
			}*/
			StringBuilder sqlDelete = new StringBuilder("DELETE FROM ");
			sqlDelete.append(getWrapperTabValidating().getStrForSql());
			if(isDeleteCascade()){
				sqlDelete.append(" CASCADE ");
			}
			appendWhere(sqlDelete,false);
			lastSql = sqlDelete.toString();
		}
		return lastSql;
	}
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public String getSqlSelect() throws SQLWrapperException{
		if(fixedSqlSelect!=null) {
			lastSql = fixedSqlSelect;
		}else{
			/*
			InterceptorWrapperSQL iw = getProp(INTERCEPTOR_WRAPPER_SQL);
			if(iw!=null){
				iw.changeBeforeGenerateSelect(this);
			}*/
			StringBuilder sqlSelect = new StringBuilder("SELECT ");
			if(isSelectDistinct()){
				sqlSelect.append("DISTINCT ");
			}
			//********* // se não setou as colunas e não for para usar * adiciona as colunas das tabelas
			ArrayList<ColSelect<?>> colsSelect = getColsSelect();
			if(colsSelect.size()==0 && !isUseAsterisk()){ 
				colsSelect = new ArrayList<>();// para não adicionar definitivamente nas cols do wrapper, deve adicionar somente para esta lista nesta operação
				for(Col<?> col : getWrapperTabValidating().getTab().getCols().values()){
					ColSelect<?> c = new ColSelect(getWrapperTabValidating(), col);
					colsSelect.add(c);
				}
				for(WrapperTab j : getTabsJoin()){
					for(Col<?> col : j.getTab().getCols().values()){
						ColSelect<?> c = new ColSelect(j, col);
						colsSelect.add(c);
					}
				}
			}//********************************
			
			if(colsSelect.size()==0){ 
				
				//TODO adicionar todas colunas ???
				//TODO registrar logs de alerta para forçar adicionar as colunas !!!
				if(isUseAsterisk()){
					sqlSelect.append("*");
				}else{
					throw new SQLWrapperException("Nenhuma coluna foi adicionada para o select!");
				}
			
			}else{
				boolean appendVirg = false;
				for(ColSelect<?> c : colsSelect){
					if(appendVirg) sqlSelect.append(',');
					sqlSelect.append(c.getStrForColSelect());
					appendVirg = true;
				}
			}
			sqlSelect.append(" FROM ");
			sqlSelect.append(getWrapperTabValidating().getStrForSql());
			
			addStrJoin(sqlSelect);// JOINS
			
			appendWhere(sqlSelect,true);// WHERE
			
			if(getColsGroupBy().size()>0){// GROUP BY
				sqlSelect.append(" GROUP BY ");
				boolean appSep = false;
				for(ColSelect<?> colGroupBy : getColsGroupBy()){
					if(appSep) sqlSelect.append(',');
					sqlSelect.append(colGroupBy.getWrapperTab().getAliasTab());
					sqlSelect.append('.');
					sqlSelect.append(colGroupBy.getCol().getName());
					appSep = true;
				}
			}
			
			if(getColsOrderBy().size()>0){// ORDER BY
				sqlSelect.append(" ORDER BY ");
				boolean appSep = false;
				for(OrderBy<?> colOrderBy : getColsOrderBy()){
					if(appSep) sqlSelect.append(',');
					sqlSelect.append(colOrderBy.getStrOrderBy());
					appSep = true;
				}
			}
			
			lastSql = sqlSelect.toString();
		}
		return lastSql;
	}
	
	
	private void addStrJoin(StringBuilder sel) throws SQLWrapperException{
		// as tabelas de join devem ser adicionadas na ordem correta e ter referência para alguma tabela adicionada antes
		
		for(WrapperTabJoin tabJoinCurrent : getTabsJoin()){
			sel.append(' ');
			sel.append(tabJoinCurrent.getStrTypeJoin());
			sel.append(' ');
			sel.append(tabJoinCurrent.getStrForSql());
			sel.append(" ON ");// ( acho que não precisa dos parenteses
			
			boolean appAnd = false;
			List<Col<?>[]> colsLinkJoin = tabJoinCurrent.getColsLinkJoin(this);
			if(colsLinkJoin.size()==0){
				throw new SQLWrapperException("colsFksJoin not found for WrapperTabJoin "+tabJoinCurrent.getTab().getName()+" and "+tabJoinCurrent.getTabJoinBefore(this).getTab().getName());
			}
			for(Col<?>[] linkedCols : colsLinkJoin){
				if(appAnd) sel.append(" AND ");
				
				WrapperTab tabJoinBefore = tabJoinCurrent.getTabJoinBefore(this);
				
				// a primeira Col deve ser da curretTab!!!! e a segunda deve ser a tabJoinBefore
				if(!linkedCols[0].getTab().equals(tabJoinCurrent.getTab())){
					throw new SQLWrapperException("WrapperCurrentTab invalid for col join. WrapperCurrentTab: '"+tabJoinCurrent.getStrForSql()+"', ColLink: '"+linkedCols[0].getNameWithTab()+"'");
				}
				if(!linkedCols[1].getTab().equals(tabJoinBefore.getTab())){
					throw new SQLWrapperException("WrapperTabJoinBefore invalid for col join. WrapperTabJoinBefore: '"+tabJoinBefore.getStrForSql()+"', ColLink: '"+linkedCols[1].getNameWithTab()+"'");
				}
				
				// deve ter o alias!
				
				sel.append(linkedCols[0].getTab().equals(tabJoinCurrent.getTab())?tabJoinCurrent.getAliasTab():tabJoinBefore.getAliasTab());
				sel.append('.');
				sel.append(linkedCols[0].getName());
				sel.append('=');
				sel.append(linkedCols[1].getTab().equals(tabJoinBefore.getTab())?tabJoinBefore.getAliasTab():tabJoinCurrent.getAliasTab());
				sel.append('.');
				sel.append(linkedCols[1].getName());
				
				appAnd = true;
			}
			//sel.append(')');// acho que não precisa dos parenteses
		}
	}
	
	// gera o WHERE
	private void appendWhere(StringBuilder sbSql, boolean isSelect) throws SQLWrapperException{
		String strBlockWhere = getBlockWhere().getStrBlock();
		if(strBlockWhere!=null && strBlockWhere.trim().length()>0){
			sbSql.append(" WHERE ");
			sbSql.append(strBlockWhere);
		}else if(!isSelect && !isPermitDeleteUpdateWithoutFiltrers()) {
			throw new SQLWrapperException("Para fazer DELETE ou UPDATE sem filtros deve-se chamar o método setPermiteDeleteUpdateSemFiltros(true)");
		}
		/*
		int lenBef = sbSql.length();
		sbSql.append(" WHERE ");
		boolean appendComp = false;
		boolean hasAnyFilter = false;
		for(SqlPart<?> w : getPartsWhere()){
			if(w.isFilter()){
				Filter<?> f = (Filter<?>)w;
				if( !f.ifEnterInWhere() ) {
					continue;
				}
				if(appendComp){
					sbSql.append(' ');
					sbSql.append(f.getOperatorBetweenFilters()); // default é AND
					sbSql.append(' ');
				}
				sbSql.append(f.getStrForWhere());
				appendComp = true;// só começa a concatenar operador entre os filtros depois que for adicionado algum filtro ...
				hasAnyFilter = true;
			}else if(w.isStrConcatWhere()){
				StrConcatWhere s = (StrConcatWhere)w;
				sbSql.append(s.getStrConcat());
				hasAnyFilter = true;
				appendComp = false;// se adicionar alguma string não adiciona o comp no próximo field, pois a str anterior pode ser algom assim: "...) AND ("
			}else{// não deve ocorrer
				throw new SQLWrapperException("Invalid wrapper class for where: "+w.getClass().getName());
			}
		}
		if(!hasAnyFilter) {
			if(!isSelect && !isPermitDeleteUpdateWithoutFiltrers()) {
				throw new SQLWrapperException("Para fazer DELETE ou UPDATE sem filtros deve-se chamar o método setPermiteDeleteUpdateSemFiltros(true)");
			} else {// deixa para emover o WHERE depois porque na maioria das vezes terá filtros e não será preciso remover
				sbSql.delete(lenBef, sbSql.length());// se não adicionou nada no SQL remove o WHERE que foi adicionado no início
			}
		}*/
	}
	
	public WrapperTab getWrapperTabAddedForCol(Col<?> col) throws SQLWrapperException{
		WrapperTab wtRoot = getWrapperTabValidating();
		if(wtRoot.getTab()==col.getTab()) {
			return wtRoot;
		}
		for(WrapperTab tj : getTabsJoin()){
			if(tj.getTab()==col.getTab()) {
				return tj;
			}
		}
		throw new SQLWrapperException("WrapperTab not found for col: "+col.getTab().getName()+"."+col.getName());
	}
	
	public WrapperTab getWrapperTabValidating() throws SQLWrapperException{
		if(getWrapperTabRoot()!=null){
			return getWrapperTabRoot();
		}else{
			throw new SQLWrapperException("Invalid table: null");
		}
	}
	
	public String getLastSql() {
		return lastSql;
	}
	
	public void setFixedSqlInsert(String fixedSqlInsert) {
		this.fixedSqlInsert = fixedSqlInsert;
	}

	public void setFixedSqlUpdate(String fixedSqlUpdate) {
		this.fixedSqlUpdate = fixedSqlUpdate;
	}

	public void setFixedSqlSelect(String fixedSqlSelect) {
		this.fixedSqlSelect = fixedSqlSelect;
	}

	public void setFixedSqlDelete(String fixedSqlDelete) {
		this.fixedSqlDelete = fixedSqlDelete;
	}

	
	public ArrayList<ColSelect<?>> getColsGroupBy() {
		if(colsGroupBy==null) colsGroupBy = new ArrayList<ColSelect<?>>();
		return colsGroupBy;
	}
	public ArrayList<ColSelect<?>> getColsSelect() {
		if(colsSelect==null) colsSelect = new ArrayList<ColSelect<?>>();
		return colsSelect;
	}
	
	public BlockWhere getBlockWhere() {
		if(blockWhere==null){
			blockWhere = BlockWhere.newBlockAND(this); //o bloco principal do where deve usar AND para os operadores, se quiser OR deve setar manualmente
		}
		return blockWhere;
	}
	public ArrayList<Entry<?>> getEntries() {
		if(entries==null) entries = new ArrayList<Entry<?>>();
		return entries;
	}
	public ArrayList<OrderBy<?>> getColsOrderBy() {
		if(colsOrderBy==null) colsOrderBy = new ArrayList<OrderBy<?>>(); 
		return colsOrderBy;
	}
	public ArrayList<WrapperTabJoin> getTabsJoin() {
		if(tabsJoin==null) tabsJoin = new ArrayList<WrapperTabJoin>();
		return tabsJoin;
	}
	public void setColsSelect(ArrayList<ColSelect<?>> colsSelect) {
		this.colsSelect = colsSelect;
	}
	public void setEntries(ArrayList<Entry<?>> entries) {
		this.entries = entries;
	}
	public void setColsOrderBy(ArrayList<OrderBy<?>> colsOrderBy) {
		this.colsOrderBy = colsOrderBy;
	}
	public void setTabsJoin(ArrayList<WrapperTabJoin> tabsJoin) {
		this.tabsJoin = tabsJoin;
	}
	public boolean isSelectDistinct() {
		return isSelectDistinct;
	}
	public void setSelectDistinct(boolean isSelectDistinct) {
		this.isSelectDistinct = isSelectDistinct;
	}
	public boolean isPermitDeleteUpdateWithoutFiltrers() {
		return permitDeleteUpdateWithoutFiltrers;
	}
	public void setPermitDeleteUpdateWithoutFiltrers(boolean ermitDeleteUpdateWithoutFiltrers) {
		this.permitDeleteUpdateWithoutFiltrers = ermitDeleteUpdateWithoutFiltrers;
	}
	public void setUseAsterisk(boolean useAsterisk) {
		this.useAsterisk = useAsterisk;
	}
	public boolean isUseAsterisk() {
		return useAsterisk;
	}
	public boolean isDeleteCascade() {
		return isDeleteCascade;
	}
	public void setDeleteCascade(boolean isDeleteCascade) {
		this.isDeleteCascade = isDeleteCascade;
	}
	
}
